package cn.quang.search.admin.service.impl;

import cn.quang.search.admin.entity.dto.Result;
import cn.quang.search.admin.entity.po.EntityDataSource;
import cn.quang.search.admin.entity.po.EntityTable;
import cn.quang.search.admin.entity.vo.meta.ColumnVO;
import cn.quang.search.admin.entity.vo.meta.TableVO;
import cn.quang.search.admin.enums.EntityDataSourceTypeEnum;
import cn.quang.search.admin.enums.EntityTableFieldTypeEnum;
import cn.quang.search.admin.mapper.EntityDataSourceMapper;
import cn.quang.search.admin.mapper.EntityTableMapper;
import cn.quang.search.admin.service.IDBMetaService;
import cn.quang.search.admin.utils.DbUtils;
import cn.quang.utils.decorator.TryCatchExecutor;
import com.google.common.collect.Lists;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.List;
import java.util.concurrent.atomic.AtomicReference;

/**
 * 读取DB的Meta信息
 */
@Service
@Slf4j
@RequiredArgsConstructor
public class DBMetaService implements IDBMetaService {
    private final EntityDataSourceMapper entityDataSourceMapper;
    private final EntityTableMapper entityTableMapper;

    private static final String DRIVER_FOR_MYSQL = "com.mysql.jdbc.Driver";
    /**
     * 测试链接
     * @param jdbcUrl jdbc
     * @param userName 用户名
     * @param password 密码
     */
    @Override
    public Result<String> testConnection(String jdbcUrl, String userName, String password) {
        Result<String> methodResult = new Result<>();
        Result<Connection> result = getMySqlConnection(jdbcUrl, userName, password);
        if(result.isFail()) {
            return methodResult.changeErr(result.getMessage());
        }
        String message = "";
        Connection connection = result.getTag();
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            String databaseProductName = metaData.getDatabaseProductName();
            String databaseProductVersion = metaData.getDatabaseProductVersion();
            message = String.format("Product(%s)，Version(%s)", databaseProductName, databaseProductVersion);
        } catch (Exception e){
            log.error("获取数据库版本信息失败", e);
            return methodResult.changeErr("获取数据库版本信息失败");
        } finally {
            DbUtils.closeConnection(connection);
        }
        return methodResult.changeSucc(message);
    }

    /**
     * 根据DB数据库获取该数据库下的所有表
     * @param dataSourceId 数据库ID
     */
    @Override
    public Result<List<TableVO>> getTables(long dataSourceId, String cookie){
        Result<List<TableVO>> methodResult = new Result<>();
        // 连接数据库
        EntityDataSource dataSource = entityDataSourceMapper.selectById(dataSourceId);
        if(dataSource == null) {
            return methodResult.changeErr("该数据源不存在，无法获取表");
        }

        int type = dataSource.getType();
        List<TableVO> tableNames = Lists.newArrayList();
        if(type == EntityDataSourceTypeEnum.MYSQL.getCode()) {
            // 获取ES下的所有表
            // 获取当前MySQLdb下的所有表
            String jdbcUrl = dataSource.getJdbcUrl();
            String userName = dataSource.getUserName();
            String password = dataSource.getPassword();
            // 连接数据库
            Result<Connection> connectionResult = getMySqlConnection(jdbcUrl, userName, password);
            if(connectionResult.isFail()) {
                return methodResult.changeErr("MySQL连接失败：" + connectionResult.getMessage());
            }
            Connection connection = connectionResult.getTag();
            AtomicReference<ResultSet> resultSet = new AtomicReference<>();
            TryCatchExecutor.executeWithNoneResult("获取MySQL表", () -> {
                DatabaseMetaData metaData = connection.getMetaData();
                ResultSet tables = metaData.getTables(null, "%", "%", new String[]{"TABLE"});
                resultSet.set(tables);
                while(tables.next()) {
                    TableVO table = new TableVO();
                    table.setTableName(tables.getString("TABLE_NAME"));
                    table.setTableDescription(tables.getString("REMARKS"));
                    tableNames.add(table);
                }
            }, (e) -> {
                log.error(String.format("获取数据源（%s）的表列表失败", dataSourceId));
            });
            DbUtils.closeResultSet(resultSet.get());
            DbUtils.closeConnection(connection);
        }
        return methodResult.changeSucc(tableNames);
    }

    /**
     * 根据表ID获取该表的所有字段
     */
    @Override
    public Result<List<ColumnVO>> getColumns(long tableId, String cookie) {
        Result<List<ColumnVO>> methodResult = new Result<>();

        EntityTable table = entityTableMapper.selectById(tableId);
        if(table == null) {
            return methodResult.changeErr("该数据表不存在，无法获取列信息");
        }
        long dataSourceId = table.getDataSourceId();
        EntityDataSource dataSource = entityDataSourceMapper.selectById(dataSourceId);
        if(dataSource == null) {
            return methodResult.changeErr("该数据表对应的数据源已不存在");
        }
        List<ColumnVO> columns = Lists.newArrayList();
        if(dataSource.getType() == EntityDataSourceTypeEnum.MYSQL.getCode()) {
            String jdbcUrl = dataSource.getJdbcUrl();
            String userName = dataSource.getUserName();
            String password = dataSource.getPassword();
            Result<Connection> connectionResult = getMySqlConnection(jdbcUrl, userName, password);
            if(connectionResult.isFail()) {
                return methodResult.changeErr("获取MySQL连接失败，无法获取表结构");
            }
            Connection connection = connectionResult.getTag();
            AtomicReference<ResultSet> resultSet = new AtomicReference<>();
            TryCatchExecutor.executeWithNoneResult("获取MySQL表", () -> {
                DatabaseMetaData metaData = connection.getMetaData();
                ResultSet columnsResultSet = metaData.getColumns(connection.getCatalog(), "%", table.getTableName(), "%");
                resultSet.set(columnsResultSet);
                while(columnsResultSet.next()) {
                    ColumnVO column = new ColumnVO();
                    column.setDic(columnsResultSet.getString("COLUMN_NAME"));
                    column.setName(columnsResultSet.getString("REMARKS"));
                    int fieldType = getMySqlFieldType(columnsResultSet.getString("TYPE_NAME"));
                    column.setQueryFieldType(fieldType);
                    column.setResultFieldType(fieldType);
                    column.setSupportQuery(false);
                    column.setSupportSort(false);
                    columns.add(column);
                }
            }, (e) -> {
                log.error(String.format("获取数据表（%s）的列失败", tableId));
            });
            DbUtils.closeResultSet(resultSet.get());
            DbUtils.closeConnection(connection);
        }
        return methodResult.changeSucc(columns);
    }

    private int getMySqlFieldType(String type) {
        type = type == null ? StringUtils.EMPTY : type.toUpperCase();
        switch (type) {
            case "TINYINT":
            case "INTEGER":
            case "INT":
                return EntityTableFieldTypeEnum.Integer.getCode();
            case "DECIMAL":
                return EntityTableFieldTypeEnum.Double.getCode();
            case "TIME":
            case "DATETIME":
            case "TIMESTAMP":
                return EntityTableFieldTypeEnum.Date.getCode();
            case "BIGINT":
                return EntityTableFieldTypeEnum.Long.getCode();
            default:
                return EntityTableFieldTypeEnum.String.getCode();
        }
    }

    /**
     * 获取MySQL连接
     * @param jdbcUrl jdbc
     * @param userName 用户名
     * @param password 密码
     */
    private Result<Connection> getMySqlConnection(String jdbcUrl, String userName, String password) {
        Result<Connection> methodResult = new Result<>();
        Connection conn;
        try {
            Class.forName(DRIVER_FOR_MYSQL);
            conn = DriverManager.getConnection(jdbcUrl, userName, password);
            return methodResult.changeSucc(conn);
        } catch (Exception e) {
            log.error("获取数据库连接失败", e);
            return methodResult.changeErr(e.getMessage());
        }
    }
}
